In this document we learn how to create interactive tables with DT. Simply put, we are learning how to transform tidy data into visually clear tables. In the overall context of the workflow, this falls into the category of transforming our data into data visualisation.
{{<expand "Note: LinkedIn Learning videos" "...">}} There are references to LinkedIn Learning videos. These are complementary but not really required as the notes below are meant to be self-contained. Some students and staff would have access for free. Do not purchase access unless you are sure you don’t have access through your organisation already. {{</expand>}}
library("tidyverse")
library("DT")
Let’s say we have a dataset, such as the set below from the ACORN-SAT dataset. Note that we are deliberately using a large dataset (this one contains around 6000 observations)
load("tidy_ACORN-SAT_data/station_data.rdata")
head(station_data, 8)
## Number year average.temp Station.name Latitude Longitude Elevation Start
## 1 2012 1910 26.4 Halls Creek -18.23 127.66 422 1910
## 2 3003 1910 27.3 Broome -17.95 122.24 7 1910
## 3 6011 1910 21.5 Carnarvon -24.89 113.67 4 1910
## 4 7045 1910 21.8 Meekatharra -26.61 118.54 517 1926
## 5 9021 1910 18.0 Perth Airport -31.93 115.98 15 1910
## 6 9518 1910 16.3 Cape Leeuwin -34.37 115.14 13 1910
## 7 9789 1910 16.7 Esperance -33.83 121.89 25 1910
## 8 10917 1910 15.0 Wandering -32.67 116.67 275 1910
With DT, we can instantly make this an interactive table by using the pipeline (%>%) operator and piping our data into the datatable() function.
station_data %>%
datatable()
We immediately have an interactive table. The remainder of this tutorial will address how to tweak the table we have. Currently here are some of the things we can do:
We notice that the first ‘column’ is just a number representing which observation of the table we are looking at. This is generally of no purpose to us, so we can remove it using the rownames argument of datatable(). We set it to FALSE.
station_data %>%
datatable(rownames = FALSE)
We also notice some of our column names are not very aesthetic. We can make them more so by:
gsub() and stringr::str_to_title() functions to create a list of nicer namescolnames argument of datatable() to change these namesThe following command replaces all dots (.s) with spaces, then capitalises the first letter of each word, and stores the new set of names in the column_title variable.
column_titles <- gsub("[.]", " ", colnames(station_data)) %>%
stringr::str_to_title()
column_titles
## [1] "Number" "Year" "Average Temp" "Station Name" "Latitude"
## [6] "Longitude" "Elevation" "Start"
We now use these as the new column names with colnames:
station_data %>%
datatable(rownames = FALSE,
colnames = column_titles)
We can already search for a given term in our data, but if we want to search for a term in a single column, we can add individual column filters using the filter argument.
position sub-argument to “top” or “bottom” to add our filterstation_data %>%
datatable(rownames = FALSE,
colnames = column_titles,
filter = list(position = "top"))
If we wish to display a number of observations which is not 10, 25, 50 or 100, we can use the options argument,
pageLength sub-argument to the number of observations we wish to displaystation_data %>%
datatable(rownames = FALSE,
colnames = column_titles,
options = list(pageLength = 7))
Now say we wish to modify the search bar so that it doesn’t say “Search:”, but instead “Keyword look-up:”.
options argument of datatable()language of options, which itself takes a list as its argumentsSearch to rename the search barstation_data %>%
datatable(rownames = FALSE,
colnames = column_titles,
options = list(pageLength = 7,
language = list(sSearch = "Keyword look-up:")))
Sometimes we will wish to display data in specific formats, such as dollars ($), a percentage (%) or otherwise. We have specific functions in DT for each of these.
sample_price %>%
datatable()
If we wish to format a column as a currency, we pipe our table into formatCurrency():
currency argument to select the currency we want (as a character)sample_price %>%
datatable() %>%
formatCurrency("Amount_Spent",
currency = "$")
If we were dealing in another currency, such as the Japanese Yen, we can specify an alternate currency:
sample_price %>%
datatable() %>%
formatCurrency("Amount_Spent",
currency = "¥")
If we wish to change the decimal places displayed, we use the additional argument digits:
sample_price %>%
datatable() %>%
formatCurrency("Amount_Spent",
currency = "$") %>%
formatCurrency("Budget",
currency = "$",
digits = 0)
Let’s say we now want to display the percentage of our budget we have spent.
sample_price <- sample_price %>%
mutate(Portion_Spent = Amount_Spent / Budget)
sample_price
## # A tibble: 6 x 4
## Date Budget Amount_Spent Portion_Spent
## <chr> <dbl> <dbl> <dbl>
## 1 21-06-2019 50 49.2 0.984
## 2 22-06-2019 50 47.2 0.944
## 3 23-06-2019 50 41.8 0.836
## 4 24-06-2019 50 52.0 1.04
## 5 25-06-2019 50 52.9 1.06
## 6 26-06-2019 50 37.8 0.755
We use the formatPercentage() function, specifying:
digits argumentsample_price %>%
datatable() %>%
formatCurrency("Amount_Spent",
currency = "$") %>%
formatCurrency("Budget",
currency = "$",
digits = 0) %>%
formatPercentage("Portion_Spent",
digits = 2)
Lastly, if we wish to format dates, we use the formatDate() function:
method column
sample_price %>%
datatable() %>%
formatCurrency("Amount_Spent",
currency = "$") %>%
formatCurrency("Budget",
currency = "$",
digits = 0) %>%
formatPercentage("Portion_Spent",
digits = 2) %>%
formatDate("Date",
method = "toDateString")
Here the date formating failed. The reason for this is that the “Date” column contains strings of D-M-Y formatted dates. The formatDate() function requires entries of class ‘date’, not strings. We can fix this using the lubridate package.
dmy() function of lubridate because our dates are in D-M-Y formatclass(sample_price$Date)
## [1] "character"
library("lubridate")
sample_price <- sample_price %>%
mutate(Date = dmy(Date))
class(sample_price$Date)
## [1] "Date"
sample_price %>%
datatable() %>%
formatCurrency("Amount_Spent",
currency = "$") %>%
formatCurrency("Budget",
currency = "$",
digits = 0) %>%
formatPercentage("Portion_Spent",
digits = 2) %>%
formatDate("Date",
method = "toDateString")
If we have tables with lots of columns, we have options to truncate these.
(Sample data provided by Climate Change in Australia)
fire_data %>%
datatable(rownames = FALSE,
options = list(pageLength = 5))
The simplest method is using the argument extensions of datatable().
fire_data %>%
datatable(rownames = FALSE,
extensions = "Responsive",
options = list(pageLength = 5))
We now have a + button that displays columns which can’t fit on our screen. There is, however, an even more responsive solution which allows the reader to select and deselect columns for viewing as they like. This requires some setup:
extensions argument to a vector containing “Responsive” and “Buttons” (ie `c(“Responsive”, “Buttons”))buttons to the options argument of datatable()I("colvis") since we want the column-visability button
I() function is technical and relates to the formatting of DTdom to the options argument of datatable()dom belowfire_data %>%
datatable(rownames = FALSE,
extensions = c("Responsive", "Buttons"),
options = list(pageLength = 5,
buttons = I("colvis"),
dom = "Bf"))
We now have a “Column visibility” button we can use to toggle columns as displayed and hidden.
dom ArgumentThis argument controls much about what we see in our tables. The argument is always a string, but, unlike many arguments we have seen in R, it is the individual letters of the string that are important.
In the above example, “Bf” means we desire Buttons and filter to be enabled, in that order. There are actually many letters we can use to enhance or change our table (several of which are described here). We may be interested in:
| Letter | Function |
|---|---|
l |
Enables length changing control |
f |
Enables the global filter |
t |
The table itself (*) |
i |
Table information summary |
p |
Page number navigation |
B |
Buttons (assuming they have been properly set up) |
(*) Of course the table is always enabled, but this option is important in controlling order. For example, if we want the filter to be below the table, we use dom = "tf":
station_data %>%
datatable(rownames = FALSE,
options = list(pageLength = 5,
dom = "tf"))
As another example, consider the argument “Bpiltf”, structuring the widget, from top to bottom, as buttons, page-navigation, summary info, length changing, the table, and the filter below:
station_data %>%
datatable(rownames = FALSE,
extensions = c("Responsive", "Buttons"),
options = list(pageLength = 5,
buttons = I("colvis"),
dom = "Bpiltf"))
Lastly, as a fun example, consider that we are not limited to just one of each letter:
station_data %>%
datatable(rownames = FALSE,
options = list(pageLength = 5,
dom = "ppfptpf"))
Maybe this would not be such a good idea in practice!
We can add a button which allows users to download the data as it appears in our table.
To do this is very similar to how we added the “colvis” buttons:
extensions argument of datatable()c("Responsive", "Buttons")options argument of datatable()buttonsstation_data %>%
datatable(rownames = FALSE,
extensions = c("Responsive", "Buttons"),
options = list(pageLength = 5,
buttons = c("colvis", "excel", "csv"),
dom = "Bf"))